from flask import Blueprint, request, jsonify
from models import db, Teacher, Title, College, TeachingRecord, Course
from sqlalchemy import func

teaching_records_bp = Blueprint('teaching_records', __name__)

# 获取符合条件的教师，支持根据学院ID和课程学时筛选
@teaching_records_bp.route('/api/teaching_records/available_teachers', methods=['GET'])
def get_available_teachers():

    # 还需要传入course_id
    # 获取请求参数
    college_id = request.args.get('college_id')  # 获取学院ID
    course_hours = request.args.get('course_hours', type=int)  # 获取课程学时
    course_id = request.args.get('course_id')
    # 检查参数是否存在，且校验类型
    if not college_id or not course_hours:
        return jsonify({'message': 'Missing required parameters'}), 400

    try:
        college_id = int(college_id)  # 将学院ID转换为整数类型
    except ValueError:
        return jsonify({'message': 'Invalid college_id parameter'}), 400

    # 获取符合条件的教师，筛选条件：学院ID，使用 outerjoin 来处理可能没有关联职称的教师
    teachers = Teacher.query.outerjoin(Title).filter(Teacher.college_id == college_id).all()

    available_teachers = []

    # 遍历符合条件的教师
    for teacher in teachers:
        # 获取教师职称的教学时限
        title_limit = teacher.title.teaching_limit if teacher.title else 0  # 如果没有职称，则默认 0

        # 计算教师已授课的总学时
        # 仅计算 is_teaching == True 的课程
        teaching_count = db.session.query(func.sum(Course.course_hours)) \
            .join(TeachingRecord, TeachingRecord.course_id == Course.course_id) \
            .filter(TeachingRecord.teacher_id == teacher.teacher_id, TeachingRecord.is_teaching == True) \
            .scalar() or 0

        # 计算剩余可分配的学时
        remaining_hours = title_limit - teaching_count

        # 需要检查教师是否已经被分配到相同的课程
        course_assigned = db.session.query(TeachingRecord).filter_by(
            teacher_id=teacher.teacher_id,course_id=course_id, is_teaching=True).first()
        # 如果剩余学时大于等于课程学时，添加到可用教师列表
        if remaining_hours >= course_hours and not course_assigned:
            available_teachers.append({
                'teacher_id': teacher.teacher_id,
                'name': teacher.name,
                'title_name': teacher.title.title_name if teacher.title else 'N/A',  # 职称名称，如果没有职称返回 'N/A'
                'teaching_count': teaching_count,  # 已授课学时
                'remaining_hours': remaining_hours  # 剩余可分配学时
            })

    # 即便是空,不返回 404,按空数组展示
    return jsonify(available_teachers)

# 分配课程给教师
@teaching_records_bp.route('/api/teaching_records/assign_course', methods=['POST'])
def assign_course_to_teacher():
    data = request.get_json()

    # 获取所需参数
    teacher_id = data.get('teacher_id')
    course_id = data.get('course_id')

    if not teacher_id or not course_id:
        return jsonify({'message': 'Missing required parameters'}), 400

    # 查询教师和课程
    teacher = Teacher.query.get(teacher_id)
    course = Course.query.get(course_id)

    if not teacher or not course:
        return jsonify({'message': 'Teacher or Course not found'}), 404

    # 检查该教师是否已经有足够的剩余学时来教授此课程
    title_limit = teacher.title.teaching_limit if teacher.title else 0
    teaching_count = db.session.query(func.sum(Course.course_hours)) \
                         .join(TeachingRecord, TeachingRecord.course_id == Course.course_id) \
                         .filter(TeachingRecord.teacher_id == teacher.teacher_id, TeachingRecord.is_teaching == True) \
                         .scalar() or 0

    remaining_hours = title_limit - teaching_count

    if remaining_hours < course.course_hours:
        return jsonify({'message': 'Not enough available hours for the teacher to teach this course'}), 400

    # 查找该教师是否已经有该课程的授课记录
    teaching_record = TeachingRecord.query.filter_by(teacher_id=teacher_id, course_id=course_id).first()

    # 记录存在但是可能没有分配
    if teaching_record:
        # 如果记录已存在，更新 teaching_count
        teaching_record.teaching_count += 1
        teaching_record.is_teaching = True
    else:
        # 如果记录不存在，创建新记录
        teaching_record = TeachingRecord(
            teacher_id=teacher_id,
            course_id=course_id,
            is_teaching=True,
            teaching_count=1
        )
        db.session.add(teaching_record)

    # 提交事务
    db.session.commit()

    return jsonify({'message': 'Course assigned successfully'})

# new add
# 获取已分配教师列表
@teaching_records_bp.route('/api/teaching_records/assigned_teachers', methods=['GET'])
def get_assigned_teachers():
    course_id = request.args.get('course_id')

    if not course_id:
        return jsonify({'message': 'Missing required parameter: course_id'}), 400

    try:
        course_id = int(course_id)
    except ValueError:
        return jsonify({'message': 'Invalid course_id parameter'}), 400

    # 查找所有已分配该课程的教师
    assigned_teachers = db.session.query(Teacher, TeachingRecord) \
        .join(TeachingRecord, TeachingRecord.teacher_id == Teacher.teacher_id) \
        .filter(TeachingRecord.course_id == course_id, TeachingRecord.is_teaching == True) \
        .all()

    # 构建返回数据
    result = []
    for teacher, record in assigned_teachers:
        total_teaching_hours = db.session.query(func.sum(Course.course_hours)) \
                                   .join(TeachingRecord, TeachingRecord.course_id == Course.course_id) \
                                   .filter(TeachingRecord.teacher_id == teacher.teacher_id,
                                           TeachingRecord.is_teaching == True) \
                                   .scalar() or 0
        remaining_hours = teacher.title.teaching_limit - total_teaching_hours if teacher.title else 0
        result.append({
            'teacher_id': teacher.teacher_id,
            'name': teacher.name,
            'title': teacher.title.title_name if teacher.title else 'N/A',
            'teaching_count': total_teaching_hours,
            'remaining_hours': teacher.title.teaching_limit - db.session.query(func.sum(Course.course_hours)) \
                .join(TeachingRecord, TeachingRecord.course_id == Course.course_id) \
                .filter(TeachingRecord.teacher_id == teacher.teacher_id, TeachingRecord.is_teaching == True) \
                .scalar() if teacher.title else 0
        })

    return jsonify(result)


# 撤销课程分配
@teaching_records_bp.route('/api/teaching_records/unassign_course', methods=['POST'])
def unassign_course():
    data = request.get_json()
    teacher_id = data.get('teacher_id')
    course_id = data.get('course_id')

    if not teacher_id or not course_id:
        return jsonify({'message': 'Missing required parameters'}), 400

    # 查找该教师的授课记录
    teaching_record = TeachingRecord.query.filter_by(teacher_id=teacher_id, course_id=course_id).first()

    if not teaching_record or not teaching_record.is_teaching:
        return jsonify({'message': 'No teaching record found or teacher not assigned to this course'}), 400

    # 标记撤销授课
    teaching_record.is_teaching = False

    # 减少授课学时
    teaching_record.teaching_count -= 1

    # 如果 teaching_count 小于 0，则将其设为 0（避免负数情况）
    if teaching_record.teaching_count < 0:
        teaching_record.teaching_count = 0

    # 提交事务
    db.session.commit()

    return jsonify({'message': 'Course unassigned successfully'})

# 结束所有课程的教学 (即全体结课)
@teaching_records_bp.route('/api/teaching_records/end_all_courses', methods=['POST'])
def end_all_courses():
    # 查找所有正在授课的教学记录
    teaching_records = TeachingRecord.query.filter_by(is_teaching=True).all()

    # 更新所有相关教学记录的 is_teaching 字段为 False
    for record in teaching_records:
        record.is_teaching = False  # 标记为未授课

    # 提交事务
    db.session.commit()

    return jsonify({'message': 'All course teaching assignments ended successfully'}), 200


# 首页api
@teaching_records_bp.route('/api/teaching_records/course_teacher_count', methods=['GET'])
def get_course_teacher_count():
    college_id = request.args.get('college_id', type=int)  # 获取前端传递的学院ID

    # 在查询开始时加入学院筛选
    query = db.session.query(
        TeachingRecord.course_id, func.count(TeachingRecord.teacher_id).label('teacher_count')
    ).filter(TeachingRecord.is_teaching == True) \
     .join(Course, Course.course_id == TeachingRecord.course_id)

    # 如果传入了学院ID，则进行学院筛选
    if college_id:
        query = query.filter(Course.college_id == college_id)

    course_teacher_count = query.group_by(TeachingRecord.course_id).all()

    result = []
    for course_id, teacher_count in course_teacher_count:
        course = Course.query.get(course_id)
        result.append({
            'course_name': course.course_name if course else 'N/A',
            'teacher_count': teacher_count
        })

    return jsonify(result)

@teaching_records_bp.route('/api/teaching_records/teacher_course_count', methods=['GET'])
def get_teacher_course_count():
    college_id = request.args.get('college_id', type=int)  # 获取前端传递的学院ID

    # 在查询开始时加入学院筛选
    query = db.session.query(
        TeachingRecord.teacher_id, func.count(TeachingRecord.course_id).label('course_count')
    ).filter(TeachingRecord.is_teaching == True) \
     .join(Teacher, Teacher.teacher_id == TeachingRecord.teacher_id) \
     .join(Course, Course.course_id == TeachingRecord.course_id)

    # 如果传入了学院ID，则进行学院筛选
    if college_id:
        query = query.filter(Course.college_id == college_id)

    teacher_course_count = query.group_by(TeachingRecord.teacher_id).all()

    result = []
    for teacher_id, course_count in teacher_course_count:
        teacher = Teacher.query.get(teacher_id)
        result.append({
            'teacher_name': teacher.name if teacher else 'N/A',
            'course_count': course_count
        })

    return jsonify(result)

@teaching_records_bp.route('/api/teaching_records/teacher_total_hours', methods=['GET'])
def get_teacher_total_hours():
    college_id = request.args.get('college_id', type=int)  # 获取前端传递的学院ID

    # 在查询开始时加入学院筛选
    query = db.session.query(
        TeachingRecord.teacher_id, func.sum(Course.course_hours).label('total_hours')
    ).join(Course, Course.course_id == TeachingRecord.course_id) \
     .filter(TeachingRecord.is_teaching == True)

    # 如果传入了学院ID，则进行学院筛选
    if college_id:
        query = query.filter(Course.college_id == college_id)

    teacher_total_hours = query.group_by(TeachingRecord.teacher_id).all()

    result = []
    for teacher_id, total_hours in teacher_total_hours:
        teacher = Teacher.query.get(teacher_id)
        result.append({
            'teacher_name': teacher.name if teacher else 'N/A',
            'total_hours': total_hours
        })

    return jsonify(result)






"""
# 统计每门课程有多少老师上课
@teaching_records_bp.route('/api/teaching_records/course_teacher_count', methods=['GET'])
def get_course_teacher_count():
    course_teacher_count = db.session.query(
        TeachingRecord.course_id, func.count(TeachingRecord.teacher_id).label('teacher_count')
    ).filter(TeachingRecord.is_teaching == True) \
     .group_by(TeachingRecord.course_id).all()

    result = []
    for course_id, teacher_count in course_teacher_count:
        course = Course.query.get(course_id)
        result.append({
            'course_name': course.course_name if course else 'N/A',
            'teacher_count': teacher_count
        })

    return jsonify(result)

# 统计每个老师的课程数量
@teaching_records_bp.route('/api/teaching_records/teacher_course_count', methods=['GET'])
def get_teacher_course_count():
    teacher_course_count = db.session.query(
        TeachingRecord.teacher_id, func.count(TeachingRecord.course_id).label('course_count')
    ).filter(TeachingRecord.is_teaching == True) \
     .group_by(TeachingRecord.teacher_id).all()

    result = []
    for teacher_id, course_count in teacher_course_count:
        teacher = Teacher.query.get(teacher_id)
        result.append({
            'teacher_name': teacher.name if teacher else 'N/A',
            'course_count': course_count
        })

    return jsonify(result)

# 统计每个老师总学时数
@teaching_records_bp.route('/api/teaching_records/teacher_total_hours', methods=['GET'])
def get_teacher_total_hours():
    teacher_total_hours = db.session.query(
        TeachingRecord.teacher_id, func.sum(Course.course_hours).label('total_hours')
    ).join(Course, Course.course_id == TeachingRecord.course_id) \
     .filter(TeachingRecord.is_teaching == True) \
     .group_by(TeachingRecord.teacher_id).all()

    result = []
    for teacher_id, total_hours in teacher_total_hours:
        teacher = Teacher.query.get(teacher_id)
        result.append({
            'teacher_name': teacher.name if teacher else 'N/A',
            'total_hours': total_hours
        })

    return jsonify(result)
    
"""

